-- extended event session to capture autostats updates
IF EXISTS(SELECT 1 FROM sys.server_event_sessions WHERE name = N'AutoStatsUpdate')
	DROP EVENT SESSION AutoStatsUpdate ON SERVER;
GO

-- create event session
CREATE EVENT SESSION AutoStatsUpdate ON SERVER 
ADD EVENT sqlserver.auto_stats(SET collect_database_name=(1)
    ACTION
	(
		sqlserver.sql_text
	)
	WHERE (sqlserver.is_system = 0 AND sqlserver.database_id > 4 AND status < 9)  -- loading without updating
) 
ADD TARGET package0.event_file
(
	SET FILENAME = N'C:\Temp\AutoStatsUpdate.xel',
		MAX_FILE_SIZE = 200, MAX_ROLLOVER_FILES = 5
);
GO

ALTER EVENT SESSION AutoStatsUpdate ON SERVER STATE = START;
GO

-- monitor the workload

-- stop the event session
ALTER EVENT SESSION AutoStatsUpdate ON SERVER STATE = STOP;
GO

-- query target data
SELECT
	DATEADD(hh,DATEDIFF(hh,GETUTCDATE(),CURRENT_TIMESTAMP),n.value('(@timestamp)[1]','datetime2')) AS 'timestamp', 
	n.value('(data[@name="database_id"]/value)[1]', 'int') AS database_id,
	n.value('(data[@name="database_name"]/value)[1]','nvarchar(128)') AS database_name, 
	n.value('(data[@name="count"]/value)[1]','int') AS 'count', 
	n.value('(data[@name="status"]/value)[1]','int') AS 'status', 
	CAST(n.value('(data[@name="duration"]/value)[1]', 'bigint')/1000.0 AS decimal(12,2)) AS duration_ms,
	n.value('(data[@name="index_id"]/value)[1]','int') AS index_id, 
	n.value('(data[@name="error"]/value)[1]','int') AS error, 
	n.value('(data[@name="object_id"]/value)[1]','int') AS 'object_id', 
	n.value('(data[@name="statistics_list"]/value)[1]','nvarchar(128)') AS statistics_list,
	n.value('(data[@name="success"]/value)[1]','bit') AS success,
	n.value('(action[@name="sql_text"]/value)[1]','nvarchar(max)') AS sql_text
FROM (
	SELECT
		CAST(event_data AS XML) AS event_data
	FROM sys.fn_xe_file_target_read_file
	-- SQL 2008 (R2)
	-- (N'C:\Temp\AutoStatsUpdate*.xel', N'C:\Temp\AutoStatsUpdate*.xem', NULL, NULL)
	-- SQL 2012 no metadata file required
	(N'C:\Temp\AutoStatsUpdate*xel', NULL, NULL, NULL)
) AS tab 
CROSS APPLY event_data.nodes('//event') AS q(n);
GO

-- cleanup
DROP EVENT SESSION AutoStatsUpdate ON SERVER;
GO